# -*- coding: utf-8 -*-
import datetime
import sys
import os
import xlsxwriter
import ibm_db
import logging

#文件格式如下：
#日期 数量 名称
#SELECT * FROM CMR.CMR 

# 设置时间格式20200816
def date_range(file_path,file_name):
    with open(os.path.join(file_path, file_name)) as f:
        datestr = f.readline()
        begin_date=datestr.split('-')[0]
        end_date=datestr.split('-')[1]
    dates = []
    dt = datetime.datetime.strptime(begin_date, '%Y%m%d')
    date = begin_date
    while date <= end_date:
        dates.append(date)
        dt = dt + datetime.timedelta(1)
        date = dt.strftime('%Y%m%d')
    return dates

#生成动态列表
def gen_list(num):
    li = []
    i = 0
    while i < num:
        li.append([])
        i += 1
    return li

# 读取sql语句，并传入时间范围参数，传入时间格式为20200816
def sql_info(file_path,file_name):
    
    with open(os.path.join(file_path, file_name)) as f:
        info = f.readlines()
    return info
#拼接SQL
def get_sql(dates,info):
    
    sql=""
    for i in range(len(dates)):
        if i<len(dates)-1:
            sql=sql+info.replace("{}",dates[i])+" UNION "
        else:
            sql=sql+info.replace("{}",dates[i])+" order by 3 "
    return sql

# 获取字段个数
def getnum(file_path,file_name,sql):
    logging.basicConfig(level=logging.INFO)
    dbinfo = "DATABASE=testdb; HOSTNAME=127.0.0.1; PORT=55000; PROTOCOL=TCPIP; UID=pcsw_usr; PWD=1qaz@WSX;"
    #sql = sql_info(file_path,file_name)[1].strip()
    try:
        conn = ibm_db.connect(dbinfo, '', '')
    except Exception as err:
        print("connect failed {}".format(repr(err)))
    else:
        logging.info(sql)
        try:
            stmt = ibm_db.exec_immediate(conn, sql)
            result = ibm_db.fetch_both(stmt)
            num=len(result.keys())//2
            return num
        except Exception as err:
            print("stmt failed {}".format(repr(err)))
        finally:
            if ibm_db.active(conn):
                ibm_db.close(conn)
            logging.info("connect success close")

# 数据库查询
def getres(file_path,file_name,sql):
    logging.basicConfig(level=logging.INFO)
    dbinfo = "DATABASE=testdb; HOSTNAME=127.0.0.1; PORT=55000; PROTOCOL=TCPIP; UID=pcsw_usr; PWD=1qaz@WSX;"
    #sql = sql_info(file_path,file_name)[1].strip()
    try:
        conn = ibm_db.connect(dbinfo, '', '')
    except Exception as err:
        print("connect failed {}".format(repr(err)))
    else:
        logging.info(sql)
        try:
            stmt = ibm_db.exec_immediate(conn, sql)
            result = ibm_db.fetch_both(stmt)
            num=len(result.keys())//2
            all_list = gen_list(num)
            while result:
                for i in range(len(all_list)):
                    all_list[i].append(result[i])
                result = ibm_db.fetch_both(stmt)
            return all_list
        except Exception as err:
            print("stmt failed {}".format(repr(err)))
        finally:
            if ibm_db.active(conn):
                ibm_db.close(conn)
            logging.info("connect success close")
    


# 数据写入Excel
def w_excel(first_row,file_path,file_name,output_file,sql):
    #if not os.path.exists(os.path.expanduser('~')+os.sep+"docs"):
    #    os.mkdir(os.path.expanduser('~')+os.sep+"docs")
    #workbook = xlsxwriter.Workbook(os.path.expanduser('~')+os.sep+"docs"+os.sep+"result.xlsx")
    workbook = xlsxwriter.Workbook(file_path+os.sep+output_file)
    worksheet = workbook.add_worksheet(output_file.replace(".xlsx",""))
    work_format = workbook.add_format()
    # 标题
    worksheet.write_row('A1', first_row, work_format)
    # 日期
    all_list=getres(file_path,file_name,sql)
    for i in range(getnum(file_path,file_name,sql)):
        #A的ASCII码为65
        worksheet.write_column(chr(i+65)+"2",all_list[i], work_format)
        #worksheet.write_column(chr(66)+"2",all_list[1], work_format)
        #worksheet.write_column(chr(67)+"2",all_list[2], work_format)
    workbook.close()

def convert(file_path,file_name,output_file,sql):
    first_row = sql_info(file_path,file_name)[0].split()
    w_excel(first_row,file_path,file_name,output_file,sql)

if __name__ == '__main__':
    #convert('D:\\wangwb\\devops\\union','保险电销.txt','保险电销.xlsx')
    file_path="D:\\wangwb\\devops\\union"
    dates=date_range(file_path,"date.txt")
    info=sql_info(file_path,'京东成功.txt')
    sql=get_sql(dates,info[1].strip())
    convert(file_path,'京东成功.txt','京东成功.xlsx',sql)